﻿using System;
using System.Data;
using System.Collections.Generic;
using Oracle.ManagedDataAccess.Client;

namespace QQZiFramework.DataBase.Oracle
{
    public class OracleDBAccess : QQZiFramework.IDBAccess
    {
        public OracleDBAccess(DBContext context)
            : base(context)
        {

        }
        public override void Create(string connectionString)
        {
            conStr = connectionString;
        }
        /// <summary>
        /// 绑定参数是否可复用，比如in = @value or name = @value ，只生成一个参数，如果为false则生成多个参数
        /// </summary>
        /// <value></value>
        public override bool ParameterMultiplexing { get{ return false; } }
        public override void BeginTransaction()
        {
            if (trans != null)
            {
                throw new Exception("有未提交的事务，无法再创建！");
            }
            OpenSql();
            trans = conn.BeginTransaction();
            mContext.LogSqlMessage("DataBase BeginTransaction", true);
        }
        public override DataTable QueryTable(string sql)
        {
            return QueryTable(sql, CommandType.Text);
        }
        public override IDataReader QueryReader(string sql, CommandType cmdType, params System.Data.IDbDataParameter[] parameters)
        {
            if (reader != null && !reader.IsClosed) throw new ClientException("有已打开的Reader，请先调用reader.close方法关闭");
            var cmd = CreateCommand(sql, cmdType, parameters, true);
            reader = cmd.ExecuteReader();
            cmd.Parameters.Clear();
            cmd = null;
            return reader;
        }
        string lastSql;
        OracleConnection conn;
        OracleTransaction trans;
        OracleDataReader reader;
        string conStr;

        private void OpenSql()
        {
            if (conn == null) conn = new OracleConnection(conStr);
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
                mContext.LogSqlMessage("******** DataBase Open (" + System.Threading.Thread.CurrentThread.ManagedThreadId + ") ********", true);
            }
        }

        public override IDbConnection GetConnection()
        {
            return conn;
        }

        private OracleCommand CreateCommand(string sql, CommandType cmdType, System.Data.IDbDataParameter[] parameters, bool query)
        {
            OpenSql();
            var cmd = new OracleCommand(sql, conn);
            if (conn.ConnectionTimeout > 60) cmd.CommandTimeout = conn.ConnectionTimeout;
            cmd.CommandText = sql;
            cmd.CommandType = cmdType;
            if (parameters != null && parameters.Length > 0)
            {
                cmd.Parameters.AddRange(parameters);
                sql += "(";
                foreach (OracleParameter parameter in parameters)
                {
                    sql += parameter.ParameterName + "=";
                    if (parameter.Value != null && parameter.Value.ToString().Length > 255) sql += "长内容";
                    else sql += parameter.Value;
                    sql += ",";
                }
                sql = sql.Substring(0, sql.Length - 1) + ")";
            }
            if (trans != null) cmd.Transaction = trans;
            lastSql = sql;
            mContext.LogSqlMessage(sql, query);
            return cmd;
        }

        public override int ExecuteCommand(string sql)
        {
            // 枚举 CommandType.text =1 , （sql语句） CommandType.StoredProcedure = 4,    （存储过程的名称）   CommandType.TableDirect = 512（表名）,
            return ExecuteCommand(sql, CommandType.Text);
        }

        public override object ExecScalar(string sql)
        {
            return ExecScalar(sql, CommandType.Text);
        }

        public override IDbDataParameter CreateParameter(string name, object value)
        {
            if (value == null) return new OracleParameter(":" + name, DBNull.Value);
            if (value is Boolean) return new OracleParameter(":" + name, Convert.ToInt32(value));
            else return new OracleParameter(":" + name, value);
        }

        public override bool AotoIdentity
        {
            get { return false; }
        }
        public override string GetNewIdentitySql(string tableName)
        {
            return "select " + tableName + "_SEQ.nextval from dual";
        }

        public override string GetInsertedIdentitySql(string tableName)
        {
            throw new NotImplementedException();
        }

        public override string ConcatSql(params string[] args) 
        {
            return string.Join("||",args);
        }

        /// <summary>
        /// 是否存在表
        /// </summary>
        public override bool IsTableExists(string tableName)
        {
            object scalar = ExecScalar("select 1 from user_tables t where table_name= upper('" + tableName + "')");
            if (scalar != null && scalar != DBNull.Value) return true;
            else return false;
        }

        public override void Dispose()
        {
            if (conn != null)
            {
                if (conn.State == ConnectionState.Open)
                {
                    if (trans != null)
                    {
                        trans.Rollback();
                        mContext.LogSqlMessage("Rollback", true);
                    }
                    if (reader != null && !reader.IsClosed) reader.Close();
                    conn.Close();
                    mContext.LogSqlMessage("-------- DataBase Close (" + System.Threading.Thread.CurrentThread.ManagedThreadId + ") --------", true);
                }
                //if (cmd != null) cmd.Dispose();
                conn.Dispose();
                conn = null;
                //cmd = null;
                trans = null;
            }
        }


        public override string GetLastSql()
        {
            return lastSql;
        }

        public override void Commit()
        {
            if (conn != null && conn.State == ConnectionState.Open)
            {
                if (trans != null)
                {
                    trans.Commit();
                    trans = null;
                    mContext.LogSqlMessage("DataBase Commit", true);
                }
            }
        }
        public override void Rollback()
        {
            if (conn != null && conn.State == ConnectionState.Open)
            {
                if (trans != null)
                {
                    trans.Rollback();
                    trans = null;
                    mContext.LogSqlMessage("DataBase Rollback", true);
                }
            }
        }
        public override void Close()
        {
            if (conn != null && conn.State == ConnectionState.Open)
            {
                if (reader != null && !reader.IsClosed) reader.Close();
                if (trans != null)
                {
                    trans.Commit();
                    trans = null;
                    mContext.LogSqlMessage("DataBase Commit", true);
                }
            }
            Dispose();
        }


        public override String PartQuerySql(string select, string table, string where, string orderby, int start, int end)
        {
            if (orderby == null) throw new ArgumentNullException("pagger query order by");
            string sql  = "select * from (select row_number() over(order by " + orderby + ") row_num, " + select + " from " + table;
                if (!string.IsNullOrEmpty(where)) sql += " where " + where;
                sql += ") t where row_num between " + start + " and " + end;
            
            return sql;
        }

        // public override String PartQuerySql(string select, string table, string where, string orderby, IDbDataParameter pageIndex, IDbDataParameter pageSize){
        //     if (orderby == null) throw new ArgumentNullException("pagger query order by");
        //     string sql = "select * from (select row_number() over(order by " + orderby + ") rownum, " + select + " from " + table;
        //         if (!string.IsNullOrEmpty(where)) sql += " where " + where;
        //         sql += ") t where rownum between (("+pageIndex.ParameterName+" - 1) * "+ pageSize.ParameterName +" + 1) and "+pageIndex.ParameterName+" * "+ pageSize.ParameterName;
        //     return sql;
        // }



        public override DataTable QueryTable(string text, CommandType cmdType, params System.Data.IDbDataParameter[] parameters)
        {
            var cmd = CreateCommand(text, cmdType, parameters, true);
            cmd.CommandType = cmdType;
            System.Data.DataTable dt = new DataTable();
            OracleDataAdapter da = new OracleDataAdapter(cmd);
            da.Fill(dt);
            cmd.Parameters.Clear();
            cmd = null;
            return dt;
        }

        public override int ExecuteCommand(string text, CommandType cmdType, params IDbDataParameter[] parameters)
        {
            var cmd = CreateCommand(text, cmdType, parameters, false);
            cmd.CommandType = cmdType;
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            cmd = null;
            return val;
        }

        public override object ExecScalar(string text, CommandType cmdType, params System.Data.IDbDataParameter[] parameters)
        {
            var cmd = CreateCommand(text, cmdType, parameters, text.IndexOf("insert", StringComparison.OrdinalIgnoreCase) == -1);
            object obj = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            cmd = null;
            return obj;
        }

        public override string GetDateConverter(int length)
        {
            return "TO_CHAR({0},'YYYY-MM-DD HH24:MI:SS')";
        }

        static Dictionary<string, string> functionFormat = new Dictionary<string, string>();

        static OracleDBAccess()
        {
            functionFormat.Add("date", "TO_CHAR({0},'YY-MM-DD')");
            functionFormat.Add("datetime", "TO_CHAR({0},'YY-MM-DD HH24:MI:SS')");
        }

        public override Dictionary<string, string> FunctionFormatMap
        {
            get { return functionFormat; }
        }


        public override void CreateTreeExtendTableIfNotExists(string treeTableName)
        {
            ExecuteCommand("if not exists ( select  id from sysobjects where name = 'A0_UserAgencys_REX' and type = 'U' ) Create TABLE " + treeTableName + "_REX( NodeId int  not null,     Deep int not null,     SubNodeId int not Null)");
        }

        public override void BulkInsert(string tableName, DataTable dataTable)
        {
            OpenSql();
            if (tableName.IndexOf("_tt_") > 0)
            {
                //临时表
                using (var bulkCopy = new OracleBulkCopy(conn))
                {
                    foreach (DataColumn dcPrepped in dataTable.Columns)
                    {
                        bulkCopy.ColumnMappings.Add(dcPrepped.ColumnName, dcPrepped.ColumnName);
                    }
                    bulkCopy.BulkCopyTimeout = 660;
                    bulkCopy.DestinationTableName = tableName;
                    bulkCopy.WriteToServer(dataTable);
                }
            }
            else
            {
                //先插入到临时表，再用语句插入，防止有触发器问题
                List<DataColumn> columnList = new List<DataColumn>();
                foreach (DataColumn column in dataTable.Columns)
                {
                    columnList.Add(column);
                }
                string tempTableNmae = CreateTempTable("TmpTable_" + tableName, columnList.ToArray(), null);//临时表不创建主键，免得插入数据异常
                var bulkCopy = new OracleBulkCopy(conn);
                try
                {
                    foreach (DataColumn dcPrepped in dataTable.Columns)
                    {
                        bulkCopy.ColumnMappings.Add(dcPrepped.ColumnName, dcPrepped.ColumnName);
                    }
                    bulkCopy.BulkCopyTimeout = 660;
                    bulkCopy.DestinationTableName = tempTableNmae;
                    bulkCopy.WriteToServer(dataTable);

                    string insertSql = "insert into " + tableName + "(" + dataTable.Columns[0].ColumnName;
                    for (int i = 1; i < dataTable.Columns.Count; i++)
                    {
                        insertSql += "," + dataTable.Columns[i].ColumnName;
                    }
                    insertSql += ") select " + dataTable.Columns[0].ColumnName;
                    for (int i = 1; i < dataTable.Columns.Count; i++)
                    {
                        insertSql += "," + dataTable.Columns[i].ColumnName;
                    }
                    insertSql += " from " + tempTableNmae;
                    ExecuteCommand(insertSql);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    bulkCopy.Dispose();
                    DropTempTable(tempTableNmae);
                }

            }
        }
        /// <summary>
        /// 获取属性的类型转为数据库中定义的类型
        /// </summary>
        public override string ConvertDataType(Type propertyType, bool withSize)
        {

            switch (propertyType.Name)
            {
                case "Int32":
                case "Int64":
                case "Single":
                case "Double":
                case "Decimal":
                    return "NUMBER";
                case "DateTime":
                    return "date";
                default:
                    if (withSize) return "VARCHAR2(255)";
                    else return "VARCHAR2";
            }
        }


        /// <summary>
        /// 创建临时表（通常是根据DataTable)，返回表名（包含#）
        /// <param name="tableName">临时表名，不包含#</param>
        /// <param name="allColumns">所有字段</param>
        /// <param name="createPrimaryKey">指定某个列名为主键，可为空</param>
        /// </summary>
        public override string CreateTempTable(string tableName, DataColumn[] allColumns, DataColumn createPrimaryKey)
        {
            if (allColumns.Length == 0) throw new ArgumentNullException("allColumns");
            string tmpName = tableName + "_tt_" + DateTime.Now.ToString("dd");
            if (IsTableExists(tmpName))
            {
                //临时表存在
                ExecuteCommand("TRUNCATE TABLE " + tmpName);
                return tmpName;
            }
            string sql = "CREATE GLOBAL TEMPORARY TABLE " + tmpName + " (";
            string columnSql = "";
            foreach (var dc in allColumns)
            {
                columnSql += "," + dc.ColumnName + " " + ConvertDataType(dc.DataType, true);
                if (dc == createPrimaryKey) columnSql += " primary key";
            }
            sql += columnSql.Substring(1) + ")  ON COMMIT PRESERVE ROWS";
            ExecuteCommand(sql);
            return tmpName;
        }

        /// <summary>
        /// 根据临时表的内容，更新物理表
        /// <param name="primaryKey">主键名</param>
        /// <param name="updateColumns">要更新的列</param>
        /// </summary>
        public override void UpdateByTempTable(string tableName, string tempTableFullName, DataColumn[] updateColumns, DataColumn primaryKeyColumn)
        {
            var primaryKey = primaryKeyColumn.ColumnName;
            string setsql = " a_" + updateColumns[0].ColumnName + "=b_" + updateColumns[0].ColumnName;
            for (int i = 1; i < updateColumns.Length; i++)
            {
                setsql += ",a_" + updateColumns[i].ColumnName + "=b_" + updateColumns[i].ColumnName;
            }
            string viewSql = "select " + string.Format("a.{0} as a_{0} ,b.{0} as b_{0}", updateColumns[0].ColumnName);
            for (int i = 1; i < updateColumns.Length; i++)
            {
                viewSql += string.Format(",a.{0} as a_{0} ,b.{0} as b_{0}", updateColumns[i].ColumnName);
            }
            viewSql += " from " + tableName + " a  join " + tempTableFullName + " b on a." + primaryKey + " = b." + primaryKey;
            string sql = "update (" + viewSql + ") set " + setsql;
            ExecuteCommand(sql);
        }


        /// <summary>
        /// 删除临时表
        /// </summary>
        public override void DropTempTable(string tableName)
        {
            ExecuteCommand("TRUNCATE TABLE " + tableName);
            ExecuteCommand("DROP TABLE " + tableName);
        }

        public override bool IsColumnExists(string tableName, string columnName)
        {
            //是否存在配置表
            object scalar = ExecScalar("SELECT  1 FROM  USER_TAB_COLUMNS where  TABLE_NAME='" + tableName.ToUpper() + "' and COLUMN_NAME ='" + columnName + "'");
            if (scalar != null && scalar != DBNull.Value) return true;
            else return false;
        }

        public override string[] GetTablePrimaryKeys(string tableName)
        {
            var lst = new List<string>();
            string sql = string.Format("SELECT a.column_name FROM all_cons_columns a JOIN all_constraints c ON a.constraint_name = c.constraint_name WHERE c.table_name ='{0}' AND c.constraint_type = 'P' ", tableName.ToUpper());
            using (IDataReader reader = QueryReader(sql, CommandType.Text, null))
            {
                while (reader.Read())
                {
                    lst.Add(reader[0].ToString());
                }
                reader.Close();
            }
            return lst.ToArray();
        }

        public override Dictionary<string, string> GetTables()
        {
            var dt = QueryTable("SELECT table_name,comments FROM all_tab_comments  WHERE  owner  = USER AND table_type ='TABLE'   AND table_name NOT LIKE 'BIN$%'");
            var result = new Dictionary<string, string>();
            foreach (DataRow row in dt.Rows)
            {
                string comment = null;
                if (row["comments"] != DBNull.Value) comment = row["comments"].ToString();
                result.Add((string)row["table_name"], comment);
            }
            return result;
        }

        public override ForeignKeyItem[] GetTableForeignKeys(string tableName)
        {
            var dt = QueryTable(string.Format(@"SELECT fk.column_name AS Field,pk.table_name AS FKTable,pk.column_name AS FKKeyField
                FROM all_cons_columns fk
                JOIN all_constraints c ON fk.constraint_name = c.constraint_name
                JOIN all_cons_columns pk ON c.r_constraint_name = pk.constraint_name 
                WHERE c.constraint_type = 'R' AND fk.table_name = '{0}' AND fk.owner = USER ", tableName.ToUpper()));
            ForeignKeyItem[] result = new ForeignKeyItem[dt.Rows.Count];
            for (var i = 0; i < result.Length; i++)
            {
                var row = dt.Rows[i];
                result[i] = new ForeignKeyItem { ColumnName = row["Field"].ToString(), ForeignTableName = row["FKTable"].ToString(), ForeignColumnName = row["FKKeyField"].ToString() };
            }
            return result;
        }


        /// <summary>
        /// 列别名处理，oracle会强制大写，需要引引号
        /// </summary>
        /// <param name="dbFileName"></param>
        /// <param name="entityFieldName"></param>
        public override void AppendFieldAlaisName(ref string dbFileName, string entityFieldName)
        {
            if(dbFileName ==entityFieldName) return;
            if (IsAllUppercase(entityFieldName)) base.AppendFieldAlaisName(ref dbFileName,entityFieldName);//如果已经是大写，则不处理
            else dbFileName += " as \"" + entityFieldName + "\"";
        }

        static bool IsAllUppercase(string input)
        {
            foreach (char c in input)
            {
                if (char.IsLetter(c) && !char.IsUpper(c))
                {
                    return false;
                }
            }
            return true;
        }
    }
}
